/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.myapp.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apt.Bean.SpeakerEntities;
import org.apt.connectDB.*;

/**
 *
 * @author Administrator
 */
public class SpeakerDao {

    public static List<SpeakerEntities> GetTopTenSpeaker() {
        List<SpeakerEntities> listSpeaker = new ArrayList<SpeakerEntities>();
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            //String sql = "Select top(5) * from Speakers order by NEWID()";
            String sql = "SELECT top 5 Speakers.ID,Speakers.FullName FROM SpeakerJoin INNER JOIN Speakers ON SpeakerJoin.SpeakerID = Speakers.ID Group by  Speakers.FullName,Speakers.ID order by count(SpeakerJoin.SeminarID) desc";
            con = MyConnect.getConnection();
            st = con.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()) {
                SpeakerEntities speaker = new SpeakerEntities();
                speaker.setID(rs.getInt("ID"));
                speaker.setFullName(rs.getString("FullName"));
//                    speaker.setImage(rs.getString("Image"));
//                    speaker.setEmail(rs.getString("Email"));
//                    speaker.setDOB(rs.getString("DOB"));
//                    speaker.setMobile(rs.getString("MobilePhone"));
//                    speaker.setAddress(rs.getString("Address"));
//                    speaker.setStatus(rs.getBoolean("Status"));
//                    speaker.setDescription(rs.getString("Description"));
                listSpeaker.add(speaker);
            }
        } catch (SQLException ex) {
            Logger.getLogger(SpeakerDao.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return listSpeaker;
    }

    public static void main(String[] args) {
        List<SpeakerEntities> a = GetTopTenSpeaker();
        System.out.println(a.size());
    }
}
